const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const { authMiddleware } = require('../middleware/auth');

// 获取统计数据
router.get('/api/stats/overview', authMiddleware, async (req, res) => {
  try {
    // 人员统计
    const [personnelStats] = await pool.execute(`
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN audit_status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved,
        SUM(CASE WHEN audit_status = 'rejected' THEN 1 ELSE 0 END) as rejected
      FROM raw_personnel
    `);

    // 装备统计
    const [weaponStats] = await pool.execute(`
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN audit_status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved,
        SUM(CASE WHEN audit_status = 'rejected' THEN 1 ELSE 0 END) as rejected
      FROM raw_weapons
    `);

    // 确保数值类型正确
    const personnelData = {
      total: Number(personnelStats[0].total) || 0,
      pending: Number(personnelStats[0].pending) || 0,
      approved: Number(personnelStats[0].approved) || 0,
      rejected: Number(personnelStats[0].rejected) || 0
    };

    const weaponData = {
      total: Number(weaponStats[0].total) || 0,
      pending: Number(weaponStats[0].pending) || 0,
      approved: Number(weaponStats[0].approved) || 0,
      rejected: Number(weaponStats[0].rejected) || 0
    };

    res.json({
      success: true,
      data: {
        personnel: personnelData,
        weapon: weaponData,
        total: {
          total: personnelData.total + weaponData.total,
          pending: personnelData.pending + weaponData.pending,
          approved: personnelData.approved + weaponData.approved,
          rejected: personnelData.rejected + weaponData.rejected
        }
      }
    });
  } catch (error) {
    console.error('获取统计数据失败:', error);
    res.status(500).json({
      success: false,
      message: '获取统计数据失败',
      error: error.message
    });
  }
});

// 获取按天统计的数据加工情况
router.get('/api/stats/daily-process', authMiddleware, async (req, res) => {
  try {
    const { days = 7 } = req.query;

    // 人员数据加工统计（按天）
    const [personnelProcess] = await pool.execute(`
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as total,
        SUM(CASE WHEN process_status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN process_status = 'failed' THEN 1 ELSE 0 END) as failed
      FROM raw_personnel
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `, [days]);

    // 装备数据加工统计（按天）
    const [weaponProcess] = await pool.execute(`
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as total,
        SUM(CASE WHEN process_status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN process_status = 'failed' THEN 1 ELSE 0 END) as failed
      FROM raw_weapons
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `, [days]);

    res.json({
      success: true,
      data: {
        personnel: personnelProcess,
        weapon: weaponProcess
      }
    });
  } catch (error) {
    console.error('获取每日加工统计失败:', error);
    res.status(500).json({
      success: false,
      message: '获取每日加工统计失败',
      error: error.message
    });
  }
});

// 获取按天统计的审核情况
router.get('/api/stats/daily-audit', authMiddleware, async (req, res) => {
  try {
    const { days = 7 } = req.query;

    // 人员审核统计（按天）
    const [personnelAudit] = await pool.execute(`
      SELECT 
        DATE(audit_time) as date,
        COUNT(*) as total,
        SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved,
        SUM(CASE WHEN audit_status = 'rejected' THEN 1 ELSE 0 END) as rejected
      FROM raw_personnel
      WHERE audit_time IS NOT NULL 
        AND audit_time >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY DATE(audit_time)
      ORDER BY date DESC
    `, [days]);

    // 装备审核统计（按天）
    const [weaponAudit] = await pool.execute(`
      SELECT 
        DATE(audit_time) as date,
        COUNT(*) as total,
        SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved,
        SUM(CASE WHEN audit_status = 'rejected' THEN 1 ELSE 0 END) as rejected
      FROM raw_weapons
      WHERE audit_time IS NOT NULL 
        AND audit_time >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY DATE(audit_time)
      ORDER BY date DESC
    `, [days]);

    res.json({
      success: true,
      data: {
        personnel: personnelAudit,
        weapon: weaponAudit
      }
    });
  } catch (error) {
    console.error('获取每日审核统计失败:', error);
    res.status(500).json({
      success: false,
      message: '获取每日审核统计失败',
      error: error.message
    });
  }
});

// 获取最近7天的趋势数据（用于图表）
router.get('/api/stats/trends', authMiddleware, async (req, res) => {
  try {
    const { days = 7 } = req.query;

    // 生成日期列表
    const dates = [];
    for (let i = days - 1; i >= 0; i--) {
      const date = new Date();
      date.setDate(date.getDate() - i);
      dates.push(date.toISOString().split('T')[0]);
    }

    // 人员数据趋势
    const [personnelTrend] = await pool.execute(`
      SELECT 
        DATE_FORMAT(DATE(created_at), '%Y-%m-%d') as date,
        COUNT(*) as imported,
        SUM(CASE WHEN process_status = 'completed' THEN 1 ELSE 0 END) as processed,
        SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved
      FROM raw_personnel
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY DATE(created_at)
      ORDER BY date
    `, [days]);

    // 装备数据趋势
    const [weaponTrend] = await pool.execute(`
      SELECT 
        DATE_FORMAT(DATE(created_at), '%Y-%m-%d') as date,
        COUNT(*) as imported,
        SUM(CASE WHEN process_status = 'completed' THEN 1 ELSE 0 END) as processed,
        SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved
      FROM raw_weapons
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY DATE(created_at)
      ORDER BY date
    `, [days]);

    // 将数据映射到完整的日期列表
    // 使用 DATE_FORMAT 后，date 已经是 'YYYY-MM-DD' 格式的字符串
    const personnelMap = new Map(
      personnelTrend.map(item => [item.date, item])
    );
    const weaponMap = new Map(
      weaponTrend.map(item => [item.date, item])
    );

    const result = dates.map(date => {
      const personnelData = personnelMap.get(date);
      const weaponData = weaponMap.get(date);
      
      return {
        date,
        personnel: {
          imported: Number(personnelData?.imported) || 0,
          processed: Number(personnelData?.processed) || 0,
          approved: Number(personnelData?.approved) || 0
        },
        weapon: {
          imported: Number(weaponData?.imported) || 0,
          processed: Number(weaponData?.processed) || 0,
          approved: Number(weaponData?.approved) || 0
        }
      };
    });

    res.json({
      success: true,
      data: result
    });
  } catch (error) {
    console.error('获取趋势数据失败:', error);
    res.status(500).json({
      success: false,
      message: '获取趋势数据失败',
      error: error.message
    });
  }
});

module.exports = router;

